﻿<%@ WebHandler Language="C#" Class="ZSMonthTaskBoard" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using System.IO;
using ReportBusiness;
/**
 * Description: 总体--月度装配试车计划
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.3.15
 */

public class ZSMonthTaskBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerNoQiTao":
                        r = getContainerNoQiTao(jObject);//任务单齐套率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSpecQiTao":
                        r = getSpecQiTao(jObject);//工序齐套统计
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getBigAndSmallPart":
                        r = getBigAndSmallPart(jObject);//大小件齐套率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEngineBigStepsContainer":
                        r = getEngineBigStepsContainer(jObject);//发动机大阶段各任务单
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.engineno,
       t.workflowstepname lastWorkflowstepname,--工作内容
       to_char(t.plannedfinishdate,'mm') || '月'  || to_char(t.plannedfinishdate,'dd') || '日' lastPlanDate,--计划节点
       t2.planNeedHours,--计划总耗时
       t2.actualUsedHours,--实际总耗时
       t2.diffHours --总差额
FROM (
  SELECT u.attributevalue engineno,ws.workflowstepname,ep.plannedfinishdate,
         '第'||row_number() OVER(PARTITION BY u.attributevalue ORDER BY ep.plannedfinishdate DESC) maxseq 
  FROM mfg_engine_data t
  LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
  LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
  LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
  LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
  LEFT JOIN orderstatus od ON od.orderstatusid = mfg.orderstatusid
  WHERE NVL(od.orderstatusname,'打开') <> '关闭'
        AND ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
) t
LEFT JOIN 
(
  SELECT tt.engineno,SUM(tt.planNeedHours) planNeedHours,SUM(tt.actualUsedHours) actualUsedHours,SUM(tt.diffHours) diffHours
  FROM (
    SELECT u.attributevalue engineno,ws.workflowstepname,
           ROUND(ep.plannedfinishdate - ep.plannedstartdate,2) * 24 planNeedHours,
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL THEN ROUND((ep.finishdate - csd.startworkdate) * 24,2) 
                ELSE 0 
           END actualUsedHours,
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL
                THEN ROUND(((ep.finishdate - csd.startworkdate) - (ep.plannedfinishdate - ep.plannedstartdate)) * 24,2) ELSE 0
           END diffHours
    FROM mfg_engine_data t
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
    LEFT JOIN (
      SELECT u.attributevalue engineno,ep.workflowstepname,csd.startworkdate,row_number() OVER(PARTITION BY ep.machineno,ep.workflowstepname  ORDER BY csd.startworkdate) seq 
      FROM containerstepdispatch csd
      LEFT JOIN enginepartplan ep ON ep.containerid = csd.containerid
      LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
      LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
      WHERE csd.factoryid = '{3}' AND u.attributevalue IS NOT NULL AND csd.startworkdate IS NOT NULL AND csd.createdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND {2}
    ) csd ON csd.engineno = u.attributevalue AND csd.workflowstepname = ws.workflowstepname AND seq = 1
    WHERE ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
  ) tt
  GROUP BY tt.engineno
) t2 ON t2.engineno = t.engineno
WHERE t.maxseq = '第1'";

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string engineno = jObject["engineno"].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " u.attributevalue = '" + engineno + "'", factoryid);
        }
        else
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " 1=1 ", factoryid);
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;

        JArray colums = JArray.Parse(jObject["colums"].ToString());
        for (int i = 0; i < colums.Count; i++)
        {
            dtResult.Columns.Add(colums[i].ToString(), typeof(object));
        }

        sql = @"
SELECT t1.*,
       CASE WHEN t1.startworkdate IS NULL AND SYSDATE - t1.plannedstartdate > 0 THEN '延期未开工'
            WHEN t1.startworkdate IS NULL AND SYSDATE - t1.plannedstartdate <= 0 THEN '未开工'
            WHEN t1.startworkdate IS NOT NULL AND t1.startworkdate - t1.plannedstartdate > 0 AND t1.finishdate IS NULL THEN '超期开工'
            WHEN t1.startworkdate IS NOT NULL AND t1.startworkdate - t1.plannedstartdate <= 0 AND t1.finishdate IS NULL THEN '按计划开工'
            WHEN t1.finishdate IS NOT NULL AND t1.finishdate > t1.plannedfinishdate AND t1.actualUsedHours > t1.planNeedHours THEN '延期超时完工'
            WHEN t1.finishdate IS NOT NULL AND t1.finishdate > t1.plannedfinishdate AND t1.actualUsedHours <= t1.planNeedHours THEN '延期完工'
            WHEN t1.finishdate IS NOT NULL AND t1.finishdate <= t1.plannedfinishdate AND t1.actualUsedHours > t1.planNeedHours THEN '超时完工'
            WHEN t1.finishdate IS NOT NULL AND t1.finishdate <= t1.plannedfinishdate AND t1.actualUsedHours <= t1.planNeedHours THEN '按计划完工'
       END status
FROM
(
    SELECT u.attributevalue engineno,ws.workflowstepname,ep.plannedstartdate,ep.plannedfinishdate,TRUNC(ep.plannedfinishdate) selectdate,
           ep.usecompleted,csd.startworkdate,ep.finishdate,
           ROUND(ep.plannedfinishdate - ep.plannedstartdate,2) * 24 planNeedHours,--计划周期
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL THEN ROUND((ep.finishdate - csd.startworkdate) * 24,2) 
                ELSE NULL 
           END actualUsedHours,--实际周期
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL
                THEN ROUND(((ep.finishdate - csd.startworkdate) - ROUND(ep.plannedfinishdate - ep.plannedstartdate,2)) * 24,2) ELSE NULL
           END diffHours,--差值
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL
                THEN CASE WHEN ROUND(((ep.finishdate - csd.startworkdate) - ROUND(ep.plannedfinishdate - ep.plannedstartdate,2)) * 24,2) > 0 THEN 'red' ELSE 'blue' END 
                ELSE ''
           END diffColor,--差值颜色
           CASE WHEN ep.usecompleted = 1 THEN '已完成'
                WHEN ep.usecompleted = 0 AND csd.startworkdate IS NOT NULL THEN '进行中'
                WHEN ep.usecompleted = 0 AND csd.startworkdate IS NULL THEN '未开始'
           END finishStatus --完工状态
    FROM mfg_engine_data t
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
    LEFT JOIN
    (
      SELECT u.attributevalue engineno,ep.workflowstepname,csd.startworkdate,row_number() OVER(PARTITION BY ep.machineno,ep.workflowstepname  ORDER BY csd.startworkdate) seq 
      FROM containerstepdispatch csd
      LEFT JOIN enginepartplan ep ON ep.containerid = csd.containerid
      LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
      LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
      WHERE csd.factoryid = '{3}' AND u.attributevalue IS NOT NULL AND csd.startworkdate IS NOT NULL 
            AND csd.createdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND {2}
    ) csd ON csd.engineno = u.attributevalue AND csd.workflowstepname = ws.workflowstepname AND seq = 1
    WHERE ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
) t1";
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string engineno = jObject["engineno"].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " u.attributevalue = '" + engineno + "'", factoryid);
        }
        else
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " 1=1 ", factoryid);
        }

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = 7; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("engineno = '{0}' AND selectdate='{1}'", dr["engineno"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dt.Select(filter);
                DataTable dtPlanSpec;
                if (drList.Length > 0)
                {
                    dtPlanSpec = drList.CopyToDataTable();
                    foreach (DataColumn col in dtPlanSpec.Columns)
                    {
                        col.ColumnName = col.ColumnName.ToLower();
                    }
                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
                }
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //任务单齐套率
    Result getContainerNoQiTao(JObject jObject)
    {
        string sql = @"
SELECT e.machineno attributevalue,
       e.containerid,e.containername,
       mfg.mfgordername,
       pb.productname,p.description,
       wfb.workflowname,e.containerno,
       DECODE(e.containerid,NULL,'未生成','已生成') taskbillstatus,e.workflowid,
       wfs.workflowstepname,wfs.sequence,
       to_char(e.plannedstartdate,'yyyy-MM-dd HH:mi') plannedstartdate, 
       to_char(e.plannedfinishdate,'yyyy-MM-dd HH:mi') plannedfinishdate,
       DECODE(wf.workflowtype,'施工工艺','临时','技通工艺','临时','正常') workflowtype,
       NVL(e.icfullsetrate*100,0) qitaolv,ma.expectmaterialdate,wf.*
FROM enginepartplan e 
INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid AND mfg.productid != e.productid
INNER JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
LEFT JOIN product p ON e.productid=p.productid
LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid=wf.workflowbaseid
LEFT JOIN(
     SELECT MAX(pm.expectmaterialdate) expectmaterialdate,im.containerno  
     FROM issueerpmaterial im   
     LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
     GROUP BY im.containerno
) ma ON ma.containerno=e.containerno
WHERE e.containerno IS NOT NULL AND e.bigsmallbomid IS NOT NULL
      AND e.machineno = '{0}' AND wfs.workflowstepname IN {1}
ORDER BY e.plannedfinishdate,wfs.sequence,mfg.mfgordername,pb.productname,e.containername ASC";
        if (jObject["workflowstepname"].ToString() == "0010-去封/集件")
        {
            sql = string.Format(sql, jObject["engineno"].ToString(), "('0020-部装','0030-传装','0040-总装')");
        }
        else
        {
            sql = string.Format(sql, jObject["engineno"].ToString(), "('0090-部装','0100-传装','0110-总装')");
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工序齐套信息
    Result getSpecQiTao(JObject jObject)
    {
        string workflowstepname = jObject["workflowstepname"].ToString();
        string checktype = "";//初检装标识
        if ((workflowstepname == "0020-部装") || (workflowstepname == "0030-传装") || (workflowstepname == "0040-总装"))
        {
            checktype = "1";
        }
        else
        {
            checktype = "2";
        }
        string sql = @"
SELECT '{1}' containerno,ws.workflowstepid,ws.workflowstepname,ws.sequence,s.opdesc,s.specno,s.specid,
       to_char(plannedstartdate,'yyyy-MM-dd hh24:mi') plannedstartdate,
       to_char(plannedfinishdate,'yyyy-MM-dd hh24:mi') plannedfinishdate,
       NVL(pcount,0) total,NVL(ccount,0) hasCollectQty,
       CASE WHEN NVL(pcount,0) = 0 THEN 0
            ELSE ROUND((NVL(ccount,0)/NVL(pcount,0)),4) * 100
       END qiTaoRate
FROM  workflowstep ws 
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
LEFT JOIN 
(
   SELECT MIN(csd.plannedstartdate) plannedstartdate,MAX(csd.plannedfinishdate) plannedfinishdate,csd.workflowstepid 
   FROM containerstepdispatch csd 
   WHERE csd.isuse = 0 AND csd.containerid='{0}' 
   GROUP BY csd.workflowstepid 
) csd ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN 
(
     SELECT COUNT(1) pcount,COUNT(DECODE((CASE WHEN confirmqty<=0 THEN 0 ELSE 1 END),0,1,NULL)) ccount, specid 
     FROM (
          SELECT pm.specid,im.workflowstepid,pm.firstassembleqty-NVL(SUM(con.confirmqty),0) confirmqty,p.description,p.productid
          FROM  issueerpmaterial im
          LEFT JOIN partmenuinfo pm  ON im.partmenuinfoid=pm.partmenuinfoid
          LEFT JOIN product p ON p.productid=pm.productid
          LEFT JOIN (
               SELECT SUM(confirmqty)confirmqty,issueerpmaterialid 
               FROM issueerpmaterialconfirmation  
               WHERE DECODE(note,NULL,'1',note) = '{3}' 
               GROUP BY issueerpmaterialid
          ) con ON con.issueerpmaterialid=im.issueerpmaterialid
          WHERE im.containerno='{1}' AND im.ischange='0' ";
        if (checktype == "2")
        {
            sql += " AND pm.checkassembleqty > 0";
        }
        sql += @"
          GROUP BY pm.firstassembleqty,pm.specid,im.workflowstepid,p.description,p.productid 
     ) GROUP BY specid 
) spe ON spe.specid=s.specid
WHERE  ws.workflowid='{2}'  
ORDER BY ws.sequence";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["containerno"].ToString(), jObject["workflowid"].ToString(), checktype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //大小件
    Result getBigAndSmallPart(JObject jObject)
    {
        string workflowstepname = jObject["workflowstepname"].ToString();
        string checktype = "";//初检装标识
        if ((workflowstepname == "0020-部装") || (workflowstepname == "0030-传装") || (workflowstepname == "0040-总装"))
        {
            checktype = "1";
        }
        else
        {
            checktype = "2";
        }
        string sql = @"
SELECT t.expectmaterialdate,t.partmenuinfoid,t.firstassembleqty,t.checkassembleqty,t.containerno,t.productname,t.productdesc,
       DECODE(t.parttype,0,'大件',1,'小件') parttype,
       SUM(t.confirmqty) confirmqty,
       CASE WHEN t.firstassembleqty = 0 THEN 0
            ELSE ROUND(SUM(t.confirmqty)/t.firstassembleqty,2) *  100
       END qitaoRate
FROM (
    SELECT pm.expectmaterialdate,pm.partmenuinfoid,pm.firstassembleqty firstassembleqty,pm.checkassembleqty,im.containerno,im.containerid, 
           cm.productname,p.description productdesc,im.issueqty,
           isc.lossqty,im.issueqty-NVL(isc.lossqty,0) spanqty,cm.productbatchno,pm.parttype,
           im.issueerpmaterialid,NVL(con.confirmqty,0) confirmqty
    FROM issueerpmaterial im                
    LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
    LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid               
    LEFT JOIN product p ON p.productid=pm.productid
    LEFT JOIN (
         SELECT SUM(confirmqty)confirmqty,issueerpmaterialid 
         FROM issueerpmaterialconfirmation 
         WHERE DECODE(note,NULL,'1',note) = '{1}' 
         GROUP BY issueerpmaterialid
    ) con ON con.issueerpmaterialid=im.issueerpmaterialid
    LEFT JOIN (
         SELECT isc.issueerpmaterialid,SUM(isc.lossqty) lossqty 
         FROM issueerpmaterialscrapinfo isc 
         GROUP BY isc.issueerpmaterialid
    ) isc ON isc.issueerpmaterialid=im.issueerpmaterialid 
    where im.containerno='{0}' and im.ischange='0'";
        if (jObject["specid"] != null && jObject["specid"].ToString() != "")
        {
            sql += string.Format(" AND pm.specid= '{0}'", jObject["specid"].ToString());
        }
        if (checktype == "2")
        {
            sql += " AND pm.checkassembleqty > 0";
        }
        sql += @" 
) t
GROUP BY t.expectmaterialdate,t.partmenuinfoid,t.firstassembleqty,t.checkassembleqty,t.containerno,t.productname,t.productdesc,t.parttype 
ORDER BY t.productname ASC";
        sql = string.Format(sql, jObject["containerno"].ToString(), checktype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //发动机大阶段各任务单
    Result getEngineBigStepsContainer(JObject jObject)
    {
        string sql = @"
SELECT ep.machineno engineno,ep.mfgordername,ep.containername,ep.containerno,ep.containerid,ep.workflowstepname bigstep,
       ep.workflowname,ep.workflowver,ep.workflowdesc,
       MAX(ws.sequence) containermaxsequence
FROM enginepartplan ep
LEFT JOIN workflowstep ws ON ws.workflowid = ep.workflowid
WHERE ep.containerno IS NOT NULL AND ep.machineno = '{0}' AND ep.workflowstepname = '{1}'
GROUP BY ep.machineno,ep.mfgordername,ep.containername,ep.containerno,ep.containerid,ep.workflowstepname,
         ep.workflowname,ep.workflowver,ep.workflowdesc";
        sql = string.Format(sql, jObject["engineno"].ToString(), jObject["workflowstepname"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        Result r = new Result();
        if (dtResult.Rows.Count == 0)
        {
            r.data = dtResult;
            r.total = Convert.ToInt32(dtResult.Rows.Count);
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }
        int columnCount = dtResult.Columns.Count;

        //查询发动机大阶段所有任务单中最大工序数
        DataTable dtMaxSeq = dal.GetDataTable(string.Format("SELECT MAX(t.containerMaxSequence) maxSequence FROM ( {0} ) t", sql));

        int colums = Convert.ToInt32(dtMaxSeq.Rows[0]["maxSequence"].ToString());
        for (int i = 0; i < colums; i++)
        {
            dtResult.Columns.Add((i + 1).ToString(), typeof(object));
        }

        //查各任务单工序完成情况
        sql = @"
SELECT cci.containerid,cci.workflowid,ep.mfgordername,ep.machineno engineno,ep.workflowstepname bigstep,
       cci.containerid,cci.containername,cci.containerno,cci.productname,cci.productdescription,cci.workflowstepname currentspec,
       ws.workflowstepname,ws.sequence,
       csd.createdate dispatchdate,
       csd.recvdate,csd.startworkdate,
       csw.createdate reportdate,
       cki.createdate checkdate,
       CASE WHEN cki.createdate IS NOT NULL THEN '已完工' 
            WHEN cki.createdate IS NULL AND csw.createdate IS NOT NULL THEN '待检验'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NOT NULL AND csd.startworkdate IS NOT NULL AND csw.createdate IS NULL THEN '待报工'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NOT NULL AND csd.startworkdate IS NULL THEN '待开工'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NULL THEN '待接收'        
            WHEN csd.createdate IS NULL THEN '待派工'
       END status
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,csd.createdate,csd.recvdate,csd.startworkdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY csd.createdate DESC) maxseq
     FROM containerstepdispatch csd
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE ep.machineno = '{0}' AND ep.workflowstepname = '{1}' AND csd.isuse = 0
) csd ON csd.containername = cci.containername AND csd.workflowstepname = ws.workflowstepname AND csd.maxseq = 1
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,csw.createdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY csw.createdate DESC) maxseq
     FROM containerstepworkreport csw
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csw.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = csw.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE ep.machineno = '{0}' AND ep.workflowstepname = '{1}' AND csw.isuse = 0 
) csw ON csw.containername = cci.containername AND csw.workflowstepname = ws.workflowstepname AND csw.maxseq = 1
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,cki.createdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY cki.createdate DESC) maxseq
     FROM containercheckinfo cki
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cki.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = cki.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE ep.machineno = '{0}' AND ep.workflowstepname = '{1}' AND cki.isuse = 0
) cki ON cki.containername = cci.containername AND cki.workflowstepname = ws.workflowstepname AND cki.maxseq = 1
LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
WHERE ep.machineno = '{0}' AND ep.workflowstepname = '{1}'";
        sql = string.Format(sql, jObject["engineno"].ToString(), jObject["workflowstepname"].ToString());

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = columnCount; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("containerid = '{0}' AND sequence='{1}'", dr["containerid"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dt.Select(filter);
                DataTable dtPlanSpec;
                if (drList.Length > 0)
                {
                    dtPlanSpec = drList.CopyToDataTable();
                    foreach (DataColumn col in dtPlanSpec.Columns)
                    {
                        col.ColumnName = col.ColumnName.ToLower();
                    }
                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
                }
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}